package net.tycmc.cty.ctyms.timejob.iems.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.dao.ITransactionDAO;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSDAO;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSTransDAO;

public class VclRoadStatDAO {
	private MyConnectDSDAO mcdsDAO = new MyConnectDSDAO();
	private MyConnectDSTransDAO mcdstDAO = new MyConnectDSTransDAO();
	private IBaseDAO baseDAO;
	private ITransactionDAO tranDAO;
	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}
	public void setTranDAO(ITransactionDAO tranDAO) {
		this.tranDAO = tranDAO;
	}
    
	/**
	 * 获取设备信息（路况统计用）
	 * @return
	 */
    public List<Map<String, Object>> getVclInfo(){
    	List<Map<String, Object>> listVcl = new ArrayList<Map<String,Object>>();
    	//获取 柳汽-百世汇通  和 福田-百世汇通 两个机构下的设备
    	String sql = "select distinct(VehicleID),VehicleNumber from v_vehicleinfo where 1 = 1 and (DBCode like '1001D1003D1005D1005%' or DBCode like '1001D1003D1006D1002%') ";
    	listVcl = baseDAO.execQuery(sql, new Object[]{});
    	return listVcl;
    }
    
	/**
	 * 获取设备的海拔高度，按照设备ID,位置时间排序
	 * @return
	 */
	public List<Map<String, Object>> queryVclAtdeInfo(String statTime,String VehicleID) {
		StringBuffer strBuf =new StringBuffer();
		String data = statTime + " 00:00:00";
		String table = GlobalDBVar.getDBFullName_Conn("Msg_Timing_"+statTime.replaceAll("-",""), "4", data);
		strBuf.append("select MsgT_Altitude as VclP_Altitude,MsgT_GpsOther as other from ");
		strBuf.append(table + " where MsgT_Vcl_ID = "+VehicleID+" and MsgT_Altitude is not null and MsgT_Altitude <> '' ");
		return this.baseDAO.execQuery(strBuf.toString(), new Object[]{});
	}

	/**
	 * 批量执行数据的插入
	 * 
	 **/
	public  boolean insertAllToTable(List<String> sqlList,List<Object[]> paramList,String isbakTable) {
		boolean isOk = this.mcdstDAO.batch(sqlList, paramList);
		if(isbakTable.equals("1")){
			MyConnectDSTransDAO connect = new MyConnectDSTransDAO(true);
			boolean isbak = connect.batch(sqlList);
			if(!isbak){
				System.out.println("路况值数据备份失败");
			}
		}
		return isOk;

	}
	
	/**
	 * 新建日表
	 * @param stattime
	 * @return
	 */
	public int createDayTable(String stattime){
		int flag = -1;
		StringBuffer strBuf =new StringBuffer();
		strBuf.append(" CREATE TABLE [dbo].[RoadChangeStat_"+stattime.replace("-", "")+"]([rcs_id] [int] IDENTITY(1,1) NOT NULL, ");
		strBuf.append(" [rcs_vclid] [int] NOT NULL,[rcs_plain] [decimal](18,0) NULL,[rcs_hill] [decimal](18,0) NULL, ");
		strBuf.append(" [rcs_marea] [decimal](18,0) NULL,[rcs_highland] [decimal](18,0) NULL,[rcs_tonums] [int] NULL,[rcs_infen] [int] NULL, ");
		strBuf.append(" [rcs_outfen] [int] NULL,[rcs_fltone] [int] NULL,[rcs_fltsec] [int] NULL, ");
		strBuf.append(" [rcs_fltthi] [int] NULL,[rcs_beplace] [varchar](8000),[rcs_stattime] [datetime] NOT NULL) ON [PRIMARY] ");
		try{
			flag = this.mcdsDAO.execUpdate(strBuf.toString(),  new Object[]{});
		}catch(Exception e){
			System.out.println("执行创建日表失败，异常："+e);
		}
		return flag;
		
	}
	
	/**
	 * 判断表是否存在
	 * @param tabName
	 * @return
	 */
	public boolean isExtTab(String tabName){
		String isExt = "select count(1) as cnt from dbo.SysObjects Where XType='U' and Name=?";
		Map<String,Object> isExtMap = baseDAO.execQueryOne(isExt, new Object[]{tabName});
		if(isExtMap.get("cnt")!=null&&"1".equals(isExtMap.get("cnt").toString()))return true;
		return false;
	}
	
	/**
	 * 清空表数据
	 * @param tabName
	 * @return
	 */
	public void truncateTable(String tabName){
		String isExt = "truncate table "+tabName+"";
		 try{
			 mcdsDAO.execUpdate(isExt, new Object[]{});
			}catch(Exception e){
				System.out.println("执行清空表数据失败，异常："+e);
			}
	}
	
	/**
	 * 初始化表数据
	 * @return
	 */
	public int initTableData(String statTime,String isbakTable) {
		int flag = -1;
		StringBuffer strBuf =new StringBuffer();
		strBuf.append(" insert into RoadChangeStat_"+statTime.replace("-", "")+" select distinct(VehicleID),null,null,null,null,null,null,null,null,null,null,null,? from V_VehicleInfo " +
				" where 1 = 1 ");
		try{
			flag = this.mcdsDAO.execInsert(strBuf.toString(),  new Object[]{statTime});
		}catch(Exception e){
			System.out.println("执行初始化日表失败，异常："+e);
		}
		if(isbakTable.equals("1")){
			MyConnectDSDAO mucdd=new MyConnectDSDAO(true);
			int bakTable = mucdd.execInsert(strBuf.toString(), new Object[]{statTime});
			if(bakTable<0){
				System.out.println("油门开度创建备份表失败");
			}
		}
		return flag;
	}
	
	/**
	 * 更新进出围栏次数及始发地
	 * @return
	 */
	public int updateFenNums(String statTime) {
		int num = -1;
		String data = statTime;
		statTime = statTime.replace("-", "");
		String table = GlobalDBVar.getDBFullName_Conn("Msg_Alarm_Fence_"+statTime, "4", data);
		StringBuffer strBuf =new StringBuffer();
		strBuf.append("update RoadChangeStat_"+statTime+" set rcs_infen = R.inNum,rcs_outfen=R.ouNum,rcs_beplace=R.beplace from (");
		strBuf.append("select MsgAF_Vcl_ID,max(S.beplace) as beplace,max(case MsgAF_AlarmType when 0 then  num else '' end) as inNum,max(case MsgAF_AlarmType when 1 then num else '' end) as ouNum from ( ");
		strBuf.append("select  MsgAF_Vcl_ID,MsgAF_AlarmType,count(*) as num,(select ','+FenceName+',' from " + table +" " +
				"inner join Organization_FenceList on MsgAF_FenceID = FenceID where T.MsgAF_Vcl_ID = MsgAF_Vcl_ID AND T.MsgAF_AlarmType = MsgAF_AlarmType " +
				"and MsgAF_AlarmType = 1 for XML path('')) as beplace FROM " + table + " T  ");
		strBuf.append("group by MsgAF_Vcl_ID,MsgAF_AlarmType)S group by S.MsgAF_Vcl_ID ) R  where R.MsgAF_Vcl_ID = rcs_vclid ");
		try{
			num = this.mcdsDAO.execUpdate(strBuf.toString(),  new Object[]{});
		}catch(Exception e){
			System.out.println("执行更新进出围栏次数失败，异常："+e);
		}
		
		return num;
	}
	
	/**
	 * 更新设备故障等级次数
	 * @return
	 */
	public int updateFltNum(String statTime) {
		int num = 0;
		StringBuffer strBuf =new StringBuffer();
		String data = statTime;
		String table1 = GlobalDBVar.getDBFullName_Conn("Msg_FaultAlarm_"+statTime.replace("-", ""), "4", data);
		String table2 = GlobalDBVar.getDBFullName_Conn("Sys_FltISM", "1", null);
		strBuf.append("update RoadChangeStat_"+statTime.replace("-", "")+" set rcs_fltone = T.oneNum,rcs_fltsec=T.secNum,rcs_fltthi=T.thiNum from (");
		strBuf.append("select msgFa_vcl_id,max(case SysISM_Level when 1 then  num else '' end) as oneNum,max(case SysISM_Level when 2 then  num else '' end) as secNum ,max(case SysISM_Level when 3 then  num else '' end) as thiNum from ( ");
		strBuf.append("select  MsgFA_Vcl_ID,SysISM_Level,count(*) as num from " + table1 +" left join " + table2 + " sf on MsgFA_SPN = sf.SysISM_SPN and MsgFA_FMI = sf.SysISM_FMI ");
		strBuf.append("where MsgFA_FaultTime>=? and MsgFA_FaultTime<=? ");
		strBuf.append("group by MsgFA_Vcl_ID,SysISM_Level)S group by S.MsgFA_Vcl_ID ) T  where T.MsgFA_Vcl_ID = rcs_vclid ");
		try{
			num = this.mcdsDAO.execUpdate(strBuf.toString(),  new Object[]{statTime+" 00:00:00",statTime+" 23:59:59"});
		}catch(Exception e){
			System.out.println("执行查询设备故障等级次数失败，异常："+e);
		}
		
		return num;
	}
	public void createDayBakTable(String stattime) {
		StringBuffer strBuf =new StringBuffer();
		strBuf.append(" CREATE TABLE [dbo].[RoadChangeStat_"+stattime.replace("-", "")+"]([rcs_id] [int] IDENTITY(1,1) NOT NULL, ");
		strBuf.append(" [rcs_vclid] [int] NOT NULL,[rcs_plain] [decimal](18,0) NULL,[rcs_hill] [decimal](18,0) NULL, ");
		strBuf.append(" [rcs_marea] [decimal](18,0) NULL,[rcs_highland] [decimal](18,0) NULL,[rcs_tonums] [int] NULL,[rcs_infen] [int] NULL, ");
		strBuf.append(" [rcs_outfen] [int] NULL,[rcs_fltone] [int] NULL,[rcs_fltsec] [int] NULL, ");
		strBuf.append(" [rcs_fltthi] [int] NULL,[rcs_beplace] [varchar](8000),[rcs_stattime] [datetime] NOT NULL) ON [PRIMARY] ");
		MyConnectDSDAO mucdd=new MyConnectDSDAO(true);
		int bakTable = mucdd.execUpdate(strBuf.toString(), new Object[]{});
		if(bakTable<0){
			System.out.println("路况值变化创建备份表失败");
		}
		
	}
	public void deleteDayTableData(String stattime) {
		StringBuffer strBuf =new StringBuffer();
		strBuf.append(" DELETE FROM RoadChangeStat_"+stattime.replace("-", "")+"");
		MyConnectDSDAO mucdd=new MyConnectDSDAO(true);
		int bakTable = mucdd.execUpdate(strBuf.toString(), new Object[]{});
		if(bakTable<0){
			System.out.println("路况值变化创建备份表失败");
		}
		
	}
}